﻿// -----------------------------------------------------------------------
// <copyright file="AspNetUsersInRolesData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db AspNetUsersInRoles
    /// </summary>
    public class AspNetUsersInRolesData
    {
        /// <summary>
        /// Get AspNetUsersInRoles By Id
        /// </summary>
        /// <param name="userId">Param userId</param>
        /// <returns>Return AspNetUsersInRoles</returns>
        public AspNetUsersInRolesEN GetAspNetUsersInRolesById(int userId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                AspNetUsersInRolesEN aspNetUsersInRoles = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesGetById, userId);
                while (reader.Read())
                {
                    aspNetUsersInRoles = new AspNetUsersInRolesEN();

                    if (!reader.IsDBNull(0))
                    {
                        aspNetUsersInRoles.UserId = reader.GetGuid(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        aspNetUsersInRoles.RoleId = reader.GetGuid(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        aspNetUsersInRoles.UserDepartmentId = reader.GetInt32(2);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return aspNetUsersInRoles;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all AspNetUsersInRoles
        /// </summary>
        /// <returns>Return list all AspNetUsersInRoles</returns>
        public IList<AspNetUsersInRolesEN> GetAspNetUsersInRoles()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<AspNetUsersInRolesEN> lstAspNetUsersInRoles = new List<AspNetUsersInRolesEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesGetAll);
                while (reader.Read())
                {
                    AspNetUsersInRolesEN aspNetUsersInRoles = new AspNetUsersInRolesEN();

                    if (!reader.IsDBNull(0))
                    {
                        aspNetUsersInRoles.UserId = reader.GetGuid(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        aspNetUsersInRoles.RoleId = reader.GetGuid(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        aspNetUsersInRoles.UserDepartmentId = reader.GetInt32(2);
                    }

                    lstAspNetUsersInRoles.Add(aspNetUsersInRoles);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstAspNetUsersInRoles;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db AspNetUsersInRoles
        /// </summary>
        /// <param name="aspNetUsersInRoles">Paarm aspNetUsersInRoles</param>
        /// <returns>Insert AspNetUsersInRoles</returns>
        public int Insert(AspNetUsersInRolesEN aspNetUsersInRoles)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.AspNetUsersInRoles.UserId, aspNetUsersInRoles.UserId),
                                    new SqlParameter(Constants.SqlParamerterName.AspNetUsersInRoles.RoleId, aspNetUsersInRoles.RoleId),
                                    new SqlParameter(Constants.SqlParamerterName.AspNetUsersInRoles.UserDepartmentId, aspNetUsersInRoles.UserDepartmentId)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db UsersInRoles
        /// </summary>
        /// <param name="usersInRoles">Param UsersInRoles</param>
        public void Update(AspNetUsersInRolesEN usersInRoles)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.AspNetUsersInRoles.UserId, usersInRoles.UserId),
                                    new SqlParameter(Constants.SqlParamerterName.AspNetUsersInRoles.RoleId, usersInRoles.RoleId),
                                    new SqlParameter(Constants.SqlParamerterName.AspNetUsersInRoles.UserDepartmentId, usersInRoles.UserDepartmentId)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete AspNetUsersInRoles by userId
        /// </summary>
        /// <param name="userId">param userId</param>
        public void Delete(int userId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesDelete, userId);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the ASP net users in roles by user department id.
        /// </summary>
        /// <param name="userDepartmentId">The user department id.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception"></exception>
        public IList<AspNetUsersInRolesEN> GetAspNetUsersInRolesByUserDepartmentId(int userDepartmentId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<AspNetUsersInRolesEN> lstAspNetUsersInRoles = new List<AspNetUsersInRolesEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesGetByUserDepartmentId, userDepartmentId);
                while (reader.Read())
                {
                    AspNetUsersInRolesEN aspNetUsersInRoles = new AspNetUsersInRolesEN();

                    if (!reader.IsDBNull(0))
                    {
                        aspNetUsersInRoles.UserId = reader.GetGuid(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        aspNetUsersInRoles.RoleId = reader.GetGuid(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        aspNetUsersInRoles.UserDepartmentId = reader.GetInt32(2);
                    }

                    lstAspNetUsersInRoles.Add(aspNetUsersInRoles);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstAspNetUsersInRoles;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Deletes the specified user id.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="roleId">The role id.</param>
        /// <param name="userDepartmentId">The user department id.</param>
        public void Delete(AspNetUsersInRolesEN aspNetUsersInRoles)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesDeleteEx, aspNetUsersInRoles.UserId, aspNetUsersInRoles.RoleId, aspNetUsersInRoles.UserDepartmentId);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the ASP net users in roles by ASP net users in roles EN.
        /// </summary>
        /// <param name="aspUsersInRoles">The ASP users in roles.</param>
        /// <returns>return AspNetUsersInRolesEN</returns>
        public AspNetUsersInRolesEN GetAspNetUsersInRolesByAspNetUsersInRolesEN(AspNetUsersInRolesEN aspUsersInRoles)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                AspNetUsersInRolesEN aspNetUsersInRoles = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesGetByAspNetUsersInRoles, aspUsersInRoles.UserId, aspUsersInRoles.RoleId, aspUsersInRoles.UserDepartmentId);
                while (reader.Read())
                {
                    aspNetUsersInRoles = new AspNetUsersInRolesEN();

                    if (!reader.IsDBNull(0))
                    {
                        aspNetUsersInRoles.UserId = reader.GetGuid(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        aspNetUsersInRoles.RoleId = reader.GetGuid(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        aspNetUsersInRoles.UserDepartmentId = reader.GetInt32(2);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return aspNetUsersInRoles;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the ASP net users in roles by role id.
        /// </summary>
        /// <param name="roleId">The role id.</param>
        /// <returns>return list AspNetUsersInRolesEN</returns>
        public IList<AspNetUsersInRolesEN> GetAspNetUsersInRolesByRoleId(Guid roleId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<AspNetUsersInRolesEN> lstAspNetUsersInRoles = new List<AspNetUsersInRolesEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneAspNetUsersInRolesGetByRoleId, roleId);
                while (reader.Read())
                {
                    AspNetUsersInRolesEN aspNetUsersInRoles = new AspNetUsersInRolesEN();

                    if (!reader.IsDBNull(0))
                    {
                        aspNetUsersInRoles.UserId = reader.GetGuid(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        aspNetUsersInRoles.RoleId = reader.GetGuid(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        aspNetUsersInRoles.UserDepartmentId = reader.GetInt32(2);
                    }

                    lstAspNetUsersInRoles.Add(aspNetUsersInRoles);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstAspNetUsersInRoles;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}